import pandas as pd
pd.set_option('display.unicode.east_asian_width', True)
df = pd.read_excel('互联网广告智能投放数据.xlsx')
df['投放时间'] = pd.to_datetime(df['投放时间']).dt.hour
df1 = df.groupby('投放时间').agg({'价格（元）': 'sum'})
df1['排名'] = df1.rank(method='first', ascending=False)
df1.sort_values(by='排名', ascending=True, inplace=True)
print('按每个投放时间的总额降序排名和升序排序：\n', df1)
df2 = pd.crosstab(index=df['渠道'], columns=df['性别'], margins_name='比例', margins=True, normalize=True)
print('渠道和性别的交叉表：\n', df2)
df = pd.get_dummies(df, prefix='', prefix_sep='', columns=['是否点击'])
df3 = pd.pivot_table(df, values='是', index='渠道', columns='性别', aggfunc='sum')
print('点击次数、渠道和性别的透视表：\n', df3)
